package com.may.wlrj.dao;

import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.may.wlrj.entity.OrganizationEntity;
import com.may.wlrj.entity.LockEntity;
import com.may.wlrj.entity.LockMissionDetailEntity;
import com.may.wlrj.entity.MissionDetailEntity;
import com.may.wlrj.entity.MissionEntity;
import com.may.wlrj.util.XinLongUtil;

@Repository
public class MissionDAO {
	private Logger log = Logger.getLogger(MissionDAO.class);
	private JdbcTemplate jdbcTemplate;

    @Autowired
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
    
    public int savemission(MissionEntity missionEntity){
    	// insert
		String sql = "INSERT INTO tbl_lockmission (id,createname,createtime,mtype,opentype,locksn,boxid,keyvalid,authuser,auth_reals,stime, etime,ckname,active,stopreason,orgid,descriptor)"
                + " VALUES (?, ?, ?, ?,?,?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try{
			return jdbcTemplate.update(sql,missionEntity.getId(), missionEntity.getCreatename(),missionEntity.getCreatetime(),missionEntity.getMtype(),missionEntity.getOpentype(),missionEntity.getLocksn(),missionEntity.getBoxid(),missionEntity.getKeyvalid(),missionEntity.getAuthuser(),missionEntity.getAuth_reals()
					,missionEntity.getStime(),missionEntity.getEtime(),	missionEntity.getCkname(),missionEntity.getActive(),missionEntity.getStopreason(),missionEntity.getOrgid(),missionEntity.getDescriptor()); 
		}catch(DuplicateKeyException dx){
			XinLongUtil.xinLongUtil.printlog(log,dx.getMessage());
			return 2;
		}
		catch(Exception e){
			XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
			return 0;
		}
    	 	
    }
    
    public int updatemission(MissionEntity missionEntity){

		// update
		String sql = "UPDATE tbl_lockmission SET stime=?,etime=?,active=?,authuser=?,auth_reals=?, locksn=?,boxid=?,keyvalid=?, descriptor=?,stopreason=? WHERE id=?";
		try{
			jdbcTemplate.update(sql, missionEntity.getStime(), missionEntity.getEtime(),missionEntity.getActive(),missionEntity.getAuthuser(),missionEntity.getAuth_reals(),
					missionEntity.getLocksn(),missionEntity.getBoxid(),missionEntity.getKeyvalid(), missionEntity.getDescriptor(),missionEntity.getStopreason(),missionEntity.getId());
		}catch(Exception e){
			XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
			return 0;
		}
		return 1;
    	
    }
    
    public int stopmission(String mid,String stopreason) {
        String sql = "UPDATE tbl_lockmission SET active=?,stopreason=? WHERE id = ?";
        try{
        	return jdbcTemplate.update(sql, 1,stopreason,mid);
        }catch(Exception e){
        	XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
        	return -1;
        }        
    }
    
    public List<MissionEntity> findByQuery(List<OrganizationEntity> orgs,String owner,String orgname,String locksn,int pageNumber,int pageSize){
    	StringBuilder sb = new StringBuilder();
    	int i = 0;
    	for(OrganizationEntity org : orgs){
    		if(i == 0){
    			sb.append(org.getOrgcode() + "'");
    		}else{
    			sb.append(" or org.Orgcode = '" + org.getOrgcode() + "'");
    		}
    		i++;
    	}
    	String sql = ""; 
    	
    	if(!locksn.equals("")) {
    		sql = "SELECT mission.*, org.Name as orgname FROM tbl_lockmission mission left join tbl_organization org on org.id = mission.orgid left join tbl_lockmission_detail detail on detail.missionid = mission.id where mission.mtype < 3 and mission.opentype = 0 and detail.locksn like '%" + locksn + "'";
    	}else {
    		sql = "SELECT mission.*, org.Name as orgname FROM tbl_lockmission mission left join tbl_organization org on org.id = mission.orgid where mission.mtype < 3 and mission.opentype = 0";
    	}
    	if(orgname.equals("")) {
    		sql += "  and (org.Orgcode = '" + sb.toString() + ")";
    	}else {
    		sql += "  and org.Name LIKE '%" + orgname + "%'";
    	}
    	if(!owner.equals("")) {
    		sql += "  and mission.auth_reals like '%" + owner + "%'";
    	}
    	
    	sql += "  order by mission.createtime desc LIMIT " + (pageNumber-1)*pageSize + "," + pageSize; 
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(MissionEntity.class));
    }
    
    public List<MissionEntity> findByQuery(String owner,String orgname,String locksn,int pageNumber,int pageSize){    	
    	String sql = ""; 
    	
    	if(!locksn.equals("")) {
    		sql = "SELECT mission.*, org.Name as orgname FROM tbl_lockmission mission left join tbl_organization org on org.id = mission.orgid left join tbl_lockmission_detail detail on detail.missionid = mission.id where mission.mtype < 3 and mission.opentype = 0 and detail.locksn like '%" + locksn + "'";
    	}else {
    		sql = "SELECT mission.*, org.Name as orgname FROM tbl_lockmission mission left join tbl_organization org on org.id = mission.orgid where mission.mtype < 3 and mission.opentype = 0";
    	}
    	if(!orgname.equals("")) {
    		sql += "  and org.Name LIKE '%" + orgname + "%'";
    	}
    	if(!owner.equals("")) {
    		sql += "  and mission.auth_reals like '%" + owner + "%'";
    	}
    	
    	sql += "  order by mission.createtime desc LIMIT " + (pageNumber-1)*pageSize + "," + pageSize; 
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(MissionEntity.class));
    }


    public int findByQueryCount(List<OrganizationEntity> orgs,String owner,String orgname,String locksn){
    	StringBuilder sb = new StringBuilder();
    	int i = 0;
    	for(OrganizationEntity org : orgs){
    		if(i == 0){
    			sb.append(org.getOrgcode() + "'");
    		}else{
    			sb.append(" or org.Orgcode = '" + org.getOrgcode() + "'");
    		}
    		i++;
    	}
    	String sql = ""; 
    	if(!locksn.equals("")) {
    		sql = "SELECT count(*) FROM tbl_lockmission mission left join tbl_organization org on org.id = mission.orgid left join tbl_lockmission_detail detail on detail.missionid = mission.id where mission.mtype < 3 and mission.opentype = 0 and detail.locksn like '%" + locksn + "'";
    	}else {
    		sql = "SELECT count(*) FROM tbl_lockmission mission left join tbl_organization org on org.id = mission.orgid where mission.mtype < 3 and mission.opentype = 0";
    	}
    	if(orgname.equals("")) {
    		sql += "  and (org.Orgcode = '" + sb.toString() + ")";
    	}else {
    		sql += "  and org.Name LIKE '%" + orgname + "%'";
    	}
    	if(!owner.equals("")) {
    		sql += "  and mission.auth_reals like '%" + owner + "%'";
    	}
    	
    	try{
    		return  jdbcTemplate.queryForObject(sql, Integer.class);
    	}catch(Exception e){
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return 0;
    	}
    }
    
    public int findByQueryCount(String owner,String orgname,String locksn){
    	String sql = ""; 
    	if(!locksn.equals("")) {
    		sql = "SELECT count(*) FROM tbl_lockmission mission left join tbl_organization org on org.id = mission.orgid left join tbl_lockmission_detail detail on detail.missionid = mission.id where mission.mtype < 3 and mission.opentype = 0 and detail.locksn like '%" + locksn + "'";
    	}else {
    		sql = "SELECT count(*) FROM tbl_lockmission mission left join tbl_organization org on org.id = mission.orgid where mission.mtype < 3 and mission.opentype = 0";
    	}
    	if(!orgname.equals("")) {
    		sql += "  and org.Name LIKE '%" + orgname + "%'";
    	}
    	if(!owner.equals("")) {
    		sql += "  and mission.auth_reals like '%" + owner + "%'";
    	}
    	
    	try{
    		return  jdbcTemplate.queryForObject(sql, Integer.class);
    	}catch(Exception e){
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return 0;
    	}
    }
    
    public List<MissionEntity> findAllotByQuery(String boxid,String orgname,String locksn,int pageNumber,int pageSize){    	
    	String sql = ""; 
    	
    	if(!locksn.equals("")) {
    		sql = "SELECT mission.*, org.Name as orgname,tlock.boxid as boxid FROM tbl_lockmission mission left join tbl_organization org on org.id = mission.orgid left join tbl_lock tlock on tlock.locksn = mission.locksn where mission.mtype = 3 and mission.locksn like '%" + locksn + "'";
    	}else {
    		sql = "SELECT mission.*, org.Name as orgname,tlock.boxid as boxid FROM tbl_lockmission mission left join tbl_organization org on org.id = mission.orgid left join tbl_lock tlock on tlock.locksn = mission.locksn where mission.mtype = 3";
    	}
    	if(!orgname.equals("")) {
    		sql += "  and org.Name LIKE '%" + orgname + "%'";
    	}
    	if(!boxid.equals("")) {
    		sql += "  and tlock.boxid like '%" + boxid + "'";
    	}
    	
    	sql += "  order by mission.createtime desc LIMIT " + (pageNumber-1)*pageSize + "," + pageSize; 
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(MissionEntity.class));
    }
    
    public int findAllotByQueryCount(String boxid,String orgname,String locksn){
    	String sql = ""; 
    	if(!locksn.equals("")) {
    		sql = "SELECT count(*) FROM tbl_lockmission mission left join tbl_organization org on org.id = mission.orgid where mission.mtype = 3 and mission.opentype = 0 and mission.locksn like '%" + locksn + "'";
    	}else {
    		sql = "SELECT count(*) FROM tbl_lockmission mission left join tbl_organization org on org.id = mission.orgid where mission.mtype = 3";
    	}
    	if(!orgname.equals("")) {
    		sql += "  and org.Name LIKE '%" + orgname + "%'";
    	}
    	if(!boxid.equals("")) {
    		sql += "  and mission.boxid like '%" + boxid + "'";
    	}
    	
    	try{
    		return  jdbcTemplate.queryForObject(sql, Integer.class);
    	}catch(Exception e){
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return 0;
    	}
    }
    
    public List<MissionEntity> findAllotByQuery(List<OrganizationEntity> orgs,String boxid,String orgname,String locksn,int pageNumber,int pageSize){
    	StringBuilder sb = new StringBuilder();
    	int i = 0;
    	for(OrganizationEntity org : orgs){
    		if(i == 0){
    			sb.append(org.getOrgcode() + "'");
    		}else{
    			sb.append(" or org.Orgcode = '" + org.getOrgcode() + "'");
    		}
    		i++;
    	}
    	String sql = ""; 
    	
    	if(!locksn.equals("")) {
    		sql = "SELECT mission.*, org.Name as orgname FROM tbl_lockmission mission left join tbl_organization org on org.id = mission.orgid where mission.mtype = 3 and mission.locksn like '%" + locksn + "'";
    	}else {
    		sql = "SELECT mission.*, org.Name as orgname FROM tbl_lockmission mission left join tbl_organization org on org.id = mission.orgid where mission.mtype = 3";
    	}
    	if(orgname.equals("")) {
    		sql += "  and (org.Orgcode = '" + sb.toString() + ")";
    	}else {
    		sql += "  and org.Name LIKE '%" + orgname + "%'";
    	}
    	if(!boxid.equals("")) {
    		sql += "  and mission.boxid like '%" + boxid + "'";
    	}
    	
    	sql += "  order by mission.createtime desc LIMIT " + (pageNumber-1)*pageSize + "," + pageSize; 
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(MissionEntity.class));
    }
    
    public int findAllotByQueryCount(List<OrganizationEntity> orgs,String boxid,String orgname,String locksn){
    	StringBuilder sb = new StringBuilder();
    	int i = 0;
    	for(OrganizationEntity org : orgs){
    		if(i == 0){
    			sb.append(org.getOrgcode() + "'");
    		}else{
    			sb.append(" or org.Orgcode = '" + org.getOrgcode() + "'");
    		}
    		i++;
    	}
    	String sql = ""; 
    	if(!locksn.equals("")) {
    		sql = "SELECT count(*) FROM tbl_lockmission mission left join tbl_organization org on org.id = mission.orgid where mission.mtype = 3 and mission.locksn like '%" + locksn + "'";
    	}else {
    		sql = "SELECT count(*) FROM tbl_lockmission mission left join tbl_organization org on org.id = mission.orgid where mission.mtype = 3";
    	}
    	if(orgname.equals("")) {
    		sql += "  and (org.Orgcode = '" + sb.toString() + ")";
    	}else {
    		sql += "  and org.Name LIKE '%" + orgname + "%'";
    	}
    	if(!boxid.equals("")) {
    		sql += "  and mission.boxid like '%" + boxid + "'";
    	}
    	
    	try{
    		return  jdbcTemplate.queryForObject(sql, Integer.class);
    	}catch(Exception e){
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return 0;
    	}
    }
    
    public List<MissionEntity> findMultiByQuery(List<OrganizationEntity> orgs,String owner,String orgname,String locksn,int pageNumber,int pageSize){
    	StringBuilder sb = new StringBuilder();
    	int i = 0;
    	for(OrganizationEntity org : orgs){
    		if(i == 0){
    			sb.append(org.getOrgcode() + "'");
    		}else{
    			sb.append(" or org.Orgcode = '" + org.getOrgcode() + "'");
    		}
    		i++;
    	}
    	String sql = ""; 
    	
    	if(!locksn.equals("")) {
    		sql = "SELECT mission.*, org.Name as orgname FROM tbl_lockmission mission left join tbl_organization org on org.id = mission.orgid left join tbl_lockmission_detail detail on detail.missionid = mission.id where mission.opentype = 1 and detail.locksn like '%" + locksn + "'";
    	}else {
    		sql = "SELECT mission.*, org.Name as orgname FROM tbl_lockmission mission left join tbl_organization org on org.id = mission.orgid where mission.opentype = 1";
    	}
    	if(orgname.equals("")) {
    		sql += "  and (org.Orgcode = '" + sb.toString() + ")";
    	}else {
    		sql += "  and org.Name LIKE '%" + orgname + "%'";
    	}
    	if(!owner.equals("")) {
    		sql += "  and mission.auth_reals like '%" + owner + "%'";
    	}
    	
    	sql += "  order by mission.createtime desc LIMIT " + (pageNumber-1)*pageSize + "," + pageSize; 
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(MissionEntity.class));
    }


    public int findMultiByQueryCount(List<OrganizationEntity> orgs,String owner,String orgname,String locksn){
    	StringBuilder sb = new StringBuilder();
    	int i = 0;
    	for(OrganizationEntity org : orgs){
    		if(i == 0){
    			sb.append(org.getOrgcode() + "'");
    		}else{
    			sb.append(" or org.Orgcode = '" + org.getOrgcode() + "'");
    		}
    		i++;
    	}
    	String sql = ""; 
    	if(!locksn.equals("")) {
    		sql = "SELECT count(*) FROM tbl_lockmission mission left join tbl_organization org on org.id = mission.orgid left join tbl_lockmission_detail detail on detail.missionid = mission.id where mission.opentype = 1 and detail.locksn like '%" + locksn + "'";
    	}else {
    		sql = "SELECT count(*) FROM tbl_lockmission mission left join tbl_organization org on org.id = mission.orgid where mission.opentype = 1";
    	}
    	if(orgname.equals("")) {
    		sql += "  and (org.Orgcode = '" + sb.toString() + ")";
    	}else {
    		sql += "  and org.Name LIKE '%" + orgname + "%'";
    	}
    	if(!owner.equals("")) {
    		sql += "  and mission.auth_reals = '%" + owner + "%'";
    	}
    	
    	try{
    		return  jdbcTemplate.queryForObject(sql, Integer.class);
    	}catch(Exception e){
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return 0;
    	}
    }
    
    public List<MissionEntity> findMissionByAuther(String owner,int pageNumber,int pageSize) {
    	String sql = "SELECT mission.*, org.Name as orgname FROM tbl_lockmission mission left join tbl_organization org on org.id = mission.orgid where mission.opentype = 0 and mission.authuser like '%" + owner + "%' order by mission.createtime desc LIMIT " + (pageNumber-1)*pageSize + "," + pageSize;
    	try {
    		return  jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(MissionEntity.class));
    	}catch(Exception e) {
    		return null;
    	}
    	
    }
    
    public int findMissionByAutherCount(String owner) {
    	String sql = "select count(*) from tbl_lockmission where opentype = 0 and authuser like '%" + owner + "%'";
    	try{
    		return  jdbcTemplate.queryForObject(sql, Integer.class);
    	}catch(Exception e){
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return 0;
    	}
    }
    
    public List<MissionEntity> findAllotMissionByAuther(String owner,int pageNumber,int pageSize) {
    	String sql = "SELECT mission.*, org.Name as orgname FROM tbl_lockmission mission left join tbl_organization org on org.id = mission.orgid where mission.mtype = 3 and mission.authuser like '%" + owner + "%' order by mission.createtime desc LIMIT " + (pageNumber-1)*pageSize + "," + pageSize;
    	try {
    		return  jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(MissionEntity.class));
    	}catch(Exception e) {
    		return null;
    	}
    	
    }
    
    public int findAllotMissionByAutherCount(String owner) {
    	String sql = "select count(*) from tbl_lockmission where mtype = 3 and authuser like '%" + owner + "%'";
    	try{
    		return  jdbcTemplate.queryForObject(sql, Integer.class);
    	}catch(Exception e){
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return 0;
    	}
    }
    
    public MissionEntity findMissionById(String missionid) {
    	String sql = "select * from tbl_lockmission where id = '" + missionid + "'";
    	try {
    		return  jdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(MissionEntity.class));
    	}catch(Exception e) {
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return null;
    	}
    	
    }

    
    public List<MissionEntity> findActiveMission() {
    	String sql = "select * from tbl_lockmission where active = 0";
    	try {
    		return  jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(MissionEntity.class));
    	}catch(Exception e) {
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return null;
    	}
    	
    }
    
    public List<MissionEntity> findActiveMissionByAuther(String auther) {
    	String sql = "select * from tbl_lockmission where active = 0 and authuser like '%" + auther + "%'";
    	try {
    		return  jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(MissionEntity.class));
    	}catch(Exception e) {
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return null;
    	}
    	
    }
    
    public List<OrganizationEntity> findSubOrg(String parentid) throws EmptyResultDataAccessException{    	
    	String sql = "SELECT DATA.* FROM(SELECT @ids as _ids,( SELECT @ids := GROUP_CONCAT(id) FROM tbl_organization WHERE FIND_IN_SET(Parentid, @ids)) as cids," + 
			"@l := @l+1 as level FROM tbl_organization, (SELECT @ids :='" + parentid + "', @l := 0 ) b WHERE @ids IS NOT NULL" + 
			") id, tbl_organization DATA WHERE FIND_IN_SET(DATA.id, ID._ids)";
    	try{
    		XinLongUtil.xinLongUtil.printlog(log,sql);
    		return  jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(OrganizationEntity.class));
    	}catch(Exception e){
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return null;
    	}
    	
    }
    
    public List<MissionEntity> findActiveSingleMissionByUser(String username) {
    	String sql = "select * from tbl_lockmission where active = 0 and createname = '" + username + "'";
    	try {
    		return  jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(MissionEntity.class));
    	}catch(Exception e) {
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return null;
    	}
    	
    }
    
    public MissionEntity findActiveJinkuMissionByAuther(String owner) {
    	String sql = "SELECT * FROM tbl_lockmission where active = 0 and authuser = '" + owner + "'  and mtype = 2";
    	try {
    		return  jdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(MissionEntity.class));
    	}catch(Exception e) {
    		return null;
    	}
    	
    }
    
    /*====================================================================tbl_lockmission_detail===============================================================================*/
    public int insertLockMissionDetail(LockMissionDetailEntity lockoperator){    	
    	String sql = "INSERT INTO tbl_lockmission_detail (id,missionid,locksn,authuser,auth_reals,stime,etime,active,stopreason)"
                + " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
    	try{
    		return jdbcTemplate.update(sql,lockoperator.getId(),lockoperator.getMissionid(),lockoperator.getLocksn(),lockoperator.getAuthuser(),lockoperator.getAuth_reals(),lockoperator.getStime(),lockoperator.getEtime(),lockoperator.getActive(),lockoperator.getStopreason());
    	}catch(Exception e){
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return 0;
    	}    	
    }
    
    public int deleteLockMissionDetail(String missionid) {
        String sql = "DELETE FROM tbl_lockmission_detail WHERE missionid=?";
        try{
        	return jdbcTemplate.update(sql, missionid);
        }catch(Exception e){
        	XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
        	return -1;
        }
        
    }
    //锁具移交时变更授权人
    public int updateLockMissionDetailAuther(LockMissionDetailEntity lockoperator) {
        String sql = "UPDATE tbl_lockmission_detail SET authuser=?,auth_reals=? WHERE missionid = ? and locksn = ?";
        try{
        	return jdbcTemplate.update(sql, lockoperator.getAuthuser(),lockoperator.getAuth_reals(),lockoperator.getMissionid(),lockoperator.getLocksn());
        }catch(Exception e){
        	XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
        	return -1;
        }        
    }
    
    public int updateLockMissionDetail_reason(String locksn,String missionid, String stopreason) {
        String sql = "UPDATE tbl_lockmission_detail SET stopreason=?,active=? WHERE locksn = ? and missionid = ?";
        try{
        	return jdbcTemplate.update(sql, stopreason,0,locksn,missionid);
        }catch(Exception e){
        	XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
        	return -1;
        }        
    }
    
    public int updateLockMissionDetail_reasonByid(String missionid,String stopreason) {
        String sql = "UPDATE tbl_lockmission_detail SET stopreason=?,active=? WHERE missionid = ? and stopreason = ?";
        try{
        	return jdbcTemplate.update(sql, stopreason,0,missionid,"");
        }catch(Exception e){
        	XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
        	return -1;
        }        
    }
    
    public LockMissionDetailEntity findDetailMissionByMissionIdAndlock(String locksn,String missionid) {
    	String sql = "select * from tbl_lockmission_detail where missionid = '" + missionid + "' and locksn = '" + locksn + "'";
    	try {
    		return  jdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(LockMissionDetailEntity.class));
    	}catch(Exception e) {
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return null;
    	}
    	
    }
    
    public LockMissionDetailEntity findDetailMissionByMissionIdAndAuther(String auther,String missionid) {
    	String sql = "select * from tbl_lockmission_detail where missionid = '" + missionid + "' and authuser like '%" + auther + "%' limit 1";
    	try {
    		return  jdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(LockMissionDetailEntity.class));
    	}catch(Exception e) {
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return null;
    	}
    	
    }

    public List<MissionDetailEntity> findMissionDetailByQuery(String owner,String missionid, String locksn) {
    	String sql = "SELECT mission.missionid as id, mission.stime,mission.etime,mission.stopreason,mission.authuser as operators,mission.auth_reals as oper_reals,mission.active,lockuser.locksn FROM tbl_lockmission_detail mission left join tbl_lock_user lockuser on lockuser.locksn = mission.locksn where mission.missionid = '" + missionid + "'";
    	if(!owner.equals("")) {
    		sql += " and lockuser.oper_reals like '%" + owner + "%'";
    	}
    	if(!locksn.equals("")) {
    		sql += " and lockuser.locksn like '%" + locksn + "'";
    	}
    	sql += " order by lockuser.locksn";
    	try {
    		return  jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(MissionDetailEntity.class));
    	}catch(Exception e) {
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return null;
    	}
    	
    }
}
